Menu

[TOC]

T-SQL : At Beginning

Database Control

Just use ctrl+N in SQL server and write code there . You can save it as a isolated .sql in a certain path later on, or it will stay in temp folder.

Basically every script code starts in such structure:

use master
--Codes here
go

Create a database:

use master
create database Databasename
on
  (name= test1_dat,filename='E:\Microsoft SQL Server\database\databasename.mdf') --main file
log on
  (name='databasename_log',filename='E:\Microsoft SQL Server\database\test1log.ldf')--log file
go

Use master means all commends are under current database.

So you need point out two file at least : main file and its log.

main file must be a .mdf file and it holds everything's location , and log file saves every records in this database.

However , a database can contains many files attached , whose format is .ndf, and they can be saved in a single file or a file group . Besides , you can define more parameters like this:

use master
go
create database test2
on
primary

(name=prim_sub1,
filename='E:\Microsoft SQL Server\database\prim_sub1_dat.mdf',--Main file
size = 6mb,
maxsize = 20mb,
filegrowth = 20%),
(name = prim_sub2,
filename='E:\Microsoft SQL Server\database\prim_sub2_dat.ndf',--A sub-file
size = 6mb,
maxsize =20mb,
filegrowth=20%),

filegroup grouptest1  --A group of sub-files , which contains 2 files.
(name = grouptest1,
filename='E:\Microsoft SQL Server\database\group1_sub1_dat.ndf',
size = 6mb,
maxsize =20mb,
filegrowth=5mb),
(name = group1_sub2,
filename='E:\Microsoft SQL Server\database\group1_sub2_dat.ndf',--log file
size = 6mb,
maxsize = 20mb,
filegrowth = 5mb)
go

Detach a database from SQL server:

If you want to move the database file , or copy/delete its file manually , you shall detach it first.

And to establish database from file , make attaching on it.

It's easy to remove a database , means your SQL server will no longer recognize it . No file will be deleted during detaching:

use master
exec sp_detach_db databsename , ture

Attaching:

use master
create database databasename
on(filename = 'PATH') --path of the MAIN FILE
for attach;
go

Create Backup:

1. Backing up a whole database :

   back up database databasename
     to disk = 'd:\backup\full.bak'
   

2. Backing up a whole database :(Rewrite every files)

   back up database databasename
     to disk = 'TARGET PATH' with init
   

3. Backing up specific files or filegroups:(Changed file compared with last whole database only)

   back up database databasename
     to disk = 'TARGET PATH' with differential
   

4. Creating a backup in many files:

   backup database databasename
   to disk='d:\backup\part1.bak',disk='d:\backup\part2.bak'
   

5. Backing up the log.

   backup log databasename To Disk='PATH'
   

   or you can use this to keep complete log info.

   backup log databasename To Disk='PATH' with No_Truncate
   

   or if you want to keep tail log:

   backup log databasename To Disk='PATH' with norecovery
   

Restore database:

simple:

restore database databasename from disk = 'd:\backup\full.bak'

Delete database (Delete file):

use master
drop database databasename

Table & Columns Control

Create Table and Columns

To start editing in a specific database , type use XXX to let you know which database will be effected. The format of creating table with its columns is : (for example)

use databaseneame
create table tablename(
    column1 char(5) not null,
    column2 char(3) null,
    column3 float null,
    primary key (column1),
    foreign key (column2) references tablename2(column2)
)

Add and Alter columns

use databasename
alter table tablename  --declare which table will be edited
add column5 char(40) not null  --add a new column to table
alter column1 char(40) not null --reset target column , which must exist first.

Delete Table

use databasename
go
drop table tablename

Insert , Update or Delete Data

There’re 3 main measures to inset new data, **Import from .xls file , directly input in SSMS , and use insert **

You can directly insert value in the format of tuple.

use databasename
insert into tablename values('value1','value2','value3',6)

When updating data:

use databasename
update tablename set[column2] = 'NewValue2' where column1 = 'value1'

You can remember this line by considering this way:

“update tablename set column to change = new value where another column helping = another column’s value”

When deleting:

use databasename
delete from tablename where column7 = 'value4'
go

Copy Table

use databasename
select * into table2 from table1 --make a copy named table2 from table1
select * into table3 from table1 where column7 = 'value4' --add a fliter for data to copy.
go